


/////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////////////////////////////////////////////////////
// 00 FOLDER SETTINGS

* clear
clear
clear 		mata
mat 		drop _all

* set path
global 		path 	""							

set maxvar 32767








* 01. Prepare linking file with Ticker
********************************************

clear
use		"$path/0 Data/CRSP/permco_gvkey_tic"


rename		LPERMCO permco
duplicates 	drop permco, force


save 	"$path/0 Data/_Processed/permco_gvkey_tic", replace




************************************************************************
************************************************************************
** 02: Baseline result: liquidity risk explains bank stock returns
************************************************************************
************************************************************************



* Load dataset
***************

clear
use		"$path/0 Data/_Processed/Data_for_Crosssection_Test_2020Q1"


** Create beta variable to reduce loss of observations (start from 60 months)
gen 	beta = .
gen 	beta_real_estate = .
gen		beta_oil = .
gen		beta_retail = .
gen		beta_aero = .
gen		beta_coal = .
gen   	beta_txtls = .
gen 	beta_cnstr = .
gen   	beta_fabpr = .

forvalues k = 12(12)60 {
		replace beta 				= bank_beta_`k'm 				if bank_beta_`k'm != . 
		replace beta_real_estate 	= bank_real_estate_beta_`k'm 	if bank_real_estate_beta_`k'm != .
		replace beta_oil			= bank_oil_beta`k'm 			if bank_oil_beta`k'm != .
		replace beta_retail			= bank_retail_beta`k'm 			if bank_retail_beta`k'm != .
		replace beta_aero			= bank_aero_beta`k'm 			if bank_aero_beta`k'm != .
		replace beta_coal			= bank_coal_beta`k'm 			if bank_coal_beta`k'm != .
		replace beta_txtls			= bank_txtls_beta`k'm 			if bank_txtls_beta`k'm != .
		replace beta_cnstr			= bank_cnstr_beta`k'm 			if bank_cnstr_beta`k'm != .
		replace beta_fabpr			= bank_fabpr_beta`k'm 			if bank_fabpr_beta`k'm != .
}
	

** Adjust variables
gen 		unused_ci_assets 			= offbs_unused_loans	/ assets_total
gen 		unused_ci_tier1 			= offbs_unused_loans	/ reg_capital_tier1
replace 	reg_tier1_ratio 			= reg_tier1_ratio / 100
replace  	current_primary_dealer 		= 0 if  current_primary_dealer==.
replace 	derivatives_to_assets		= 0 if derivatives_to_assets == .
	

** Remove Banks with missing inforamtion on key variables
egen missing = rowmiss(return_2020Q1_23_03 beta liquidity_risk npl_loans nonintinc log_assets capital roa deposits_loans)

keep if missing == 0


** Compute beta adjusted return
foreach var of varlist return_2020Q1_23_03 return_jan2020 return_feb2020 return_mar2020 {
	
	gen	`var'_adj	= `var' - beta * SP500_`var'
	
	}


gen beta_adj = beta * SP500_return_2020Q1_23_03	

foreach var of varlist return_2020Q1_23_03 return_jan2020 return_feb2020 return_mar2020 {
	
	gen	beta_adj_`var' 	=  beta * SP500_`var'
	
	}
	
** Keep only observation from 2019 Q4 (for fundamentals)	
drop if year == 2020	
	
** Identify SNL Banks
merge 	1:1 entity using "$path/0 Data/_Processed/SNL_Banks_Crosssection"
drop 	if _merge == 2
gen 	snl_bank = (_merge == 3)
drop 	_merge

** Remove other financial institutions
*drop  	if Ind == "Broker-Dealer" | Ind == "Specialty Lender"
*keep 	if (Ind == "Bank" | Ind == "Savings Bank/Thrift/Mutual" | Ind == "")



** Label variables for output
label var liquidity_risk 			"Liquidity Risk"
label var unused_ci_assets 			"Unused C&I Loans / Assets"
label var unused_ci_tier1			"Unused C&I Loans / Tier-1"
label var liquidity_assets 			"Liquidity / Assets"
label var wholesale_assets 			"Wholesale Funding / Assets"
label var npl_loans 				"NPL / Loans"
label var capital 					"Equity Ratio"
label var nonintinc 				"Non-Interest Income"
label var non_interest				"Non-Interest Income"
label var log_assets 				"Log(Assets)"
label var roa 						"ROA"
label var deposits_loans 			"Deposits / Loans"
label var reg_tier1_ratio 			"Tier-1 Ratio"
label var income_diversity   		"Income Diversity"
label var log_Z  					"Distance-to-Default"
label var loans_assets 				"Loans / Assets"
label var deposits_assets  			"Deposits / Assets"
label var rmse_60 					"Idiosyncratic Volatility"
label var beta_real_estate 			"Real Estate Beta"
label var current_primary_dealer 	"Current Primary Dealer Indicator"
label var derivatives_to_assets 	"Derivatives / Assets"
label var beta						"Equity Beta"



* Keep only SNL banks (as in previous draft)
*********************************************

*	keep	if snl_bank == 1		// we enlarge our sample to include all publicly listed banks with non-missing variables





* Merge Ticker as identifier to merge exposure data
***********************************************************


	merge 1:1 permco using "$path/0 Data/_Processed/permco_gvkey_tic"

	keep if _merge == 3

	drop _merge
	
	ren tic Ticker
	
	
	
* Merge sectoral exposure data
*****************************************

	merge 1:1 Ticker using "$path/0 Data/_Processed/temp_exposure_analysis.dta"

		
	drop if _merge == 2
	drop _merge 
		
		
	merge n:1 LenderParentId using  "$path/0 Data/_Processed/US_exp_lenders_US.dta"
		
	drop if _merge == 2
	drop _merge 
		
	
	
	* Off B/S credit card exposure
	***********************************
	
	generate	offbs_unused_cc_ta			=	offbs_unused_cc / assets_total
		
	
	
	
	
	* Consumer loans
	*******************
	
	generate	assets_loans_consumer_ta	=	assets_loans_consumer / assets_total
	
	
	
	* Oil exposure
	********************
	
	generate	ta 			= assets_total/1000
	generate 	oil_asset 	= lender_amt / ta
	replace 	oil_asset 	= 0 if oil_asset == .
	generate	exposure_d 	= (lender_amt == .)
	
	
	* Other sector exposures
	******************************

	gen sectoral_assets = (exposureRetail + exposureHotel + exposureLeisure)/ta
	replace sectoral_assets = 0 if sectoral_assets == .
	
	
	
	
	* Merge deposits / drawdown ratios from BHC data
	******************************************************
	
	
	merge 1:1 entity using "$path/0 Data/_Processed/channel ratios"
	
	keep if _merge == 3
	drop _merge
	
	
	
	* Merge with SRISK
	*******************************
	
	merge 1:1 Ticker using "$path/0 Data/_Processed/srisk_31Dec2019.dta"
	
	drop if _merge == 2
	drop _merge
	
	generate	d_nosrisk = (srisk != .)
	generate	srisk2		=	srisk
	replace		srisk2		=	0 if srisk == .
	replace		srisk2		=	0 if srisk < 0
	
	generate	srisk2_asset	= srisk2 / ta
	
	
	
	generate	lrmes2	=	lrmes
	replace		lrmes2		=	0 if lrmes == .	
	
	generate	log_lrmes2	= log(1+lrmes2)
	
	
	
	* Merge SRISK-C
	*********************+
	
	merge 1:1 Ticker using  "$path/0 Data/_Processed/srisk_c"
	
	drop _merge
	
	
	generate	srisk_plus_c		= srisk + SRISK_C_max
	replace		srisk_plus_c		=	0 if srisk_plus_c == .
	replace		srisk_plus_c		=	0 if srisk_plus_c < 0	
	
	generate	srisk_plus_c_asset	= srisk_plus_c / ta	
	
	
	generate	srisk_c_max_asset	= SRISK_C_max / ta
	
	
	
	
	* Merge yields from secondary loan market
	*********************************************
	
	gen 		merge_date = mdy(3,23,2020)
	format		merge_date %td
	
	merge 		n:1 merge_date using "$path/0 Data/_Processed/yields_industries"
	
	drop 		_merge
	
	
	
* Capital interactions

	generate 	gross_dd_capital  		= change_unused_ci_ta * capital
	generate 	gross_dd_capital_100  	= (gross_dd_capital) * 1000
	xtile 		capital_quartile		= capital,n(4)
	generate	low_capital 			= (capital_quartile == 1)
	generate	high_capital 			= (capital_quartile == 4 | capital_quartile == 3)


	generate 	gross_dd_capital_d  	= change_unused_ci_ta * low_capital
	generate 	gross_dd_capital_d_high = change_unused_ci_ta * high_capital

	* Buffer
	**********
	egen		capital_mean 			= mean(capital)
	generate 	buffer 					= capital - capital_mean
	generate 	gross_dd_buffer 		= change_unused_ci_ta * buffer
	generate 	gross_dd_buffer_100 	= (gross_dd_buffer) * 100

	generate	net_dd_buffer			=  net_drawdowns * buffer
	generate	net_dd_buffer_100		=  (net_dd_buffer) * 100	
	generate 	net_dd_capital_d_high 	= net_drawdowns * high_capital	

	* SRISK x Capital 
	
	generate	srisk2_capital_high		= srisk2_asset * high_capital
	generate	srisk2_buffer_100		= srisk2_asset * buffer * 100
	
	
	* Interaction term with ROA -> profitability
	****************************************************
	
	generate 	gross_dd_roa	  		= change_unused_ci_ta * roa
	
	xtile 		roa_quartile			= roa,n(4)
	generate	low_roa 				= (roa_quartile == 1)
	generate	high_roa 				= (roa_quartile == 4 | roa_quartile == 3)	
	generate 	gross_dd_roa_low	  	= change_unused_ci_ta * low_roa
	generate 	gross_dd_roa_high	  	= change_unused_ci_ta * high_roa	
	
* Interactions with Deposits

	generate	deposits_buffer			=  change_deposits_ta * buffer
	generate	deposits_buffer_100		=  (deposits_buffer) * 100	
	generate 	deposits_capital_d_high = change_deposits_ta * high_capital	
	
	
	
	
* Merge with industry betas 
********************************+
** We use these betas as measures of bank industry exposures
** Construction: See xxx

/*
	
merge 1:1 tic using "$path/0 Data/_Processed/Beta_allind_daily.dta"
	
gen no_beta = (_merge ==1)
drop if _merge == 2
drop _merge	
replace beta_all = 0 if beta_all == .



* merge betas interacted with spreads (changes in returns during COVID)		
merge 1:1 Ticker using "$path/0 Data/_Processed/Beta_ind_daily_check_spreads.dta"
drop if _merge == 2
drop _merge
	
	
foreach var of varlist beta_211-beta_yield_722 {
		replace `var' = 0 if `var'==.
	}
	
*/


	
* Merge with industry dealscan exposures 
********************************+
** We use these exposures to relate to beta estimates
** Construction: See Fahlenbrach et al. (2021)


merge 1:1 Ticker using "$path/0 Data/_Processed/Betas_Exposure"
		
	
gen no_beta = (_merge ==1)
drop if _merge == 2
drop _merge	




	
	
* Merge Liquidity measures from Berger and Bouwman and Krishnamurthy	************************************************************************
drop 		if entity == .	
merge 		1:1 entity using "$path/0 Data/_Processed/BHC_Liquidity_Measures_Summary_New"
keep 		if _merge == 3
drop 		_merge
	
	
	

* Merge alternative equity beta
***********************************

merge 		1:1 gvkey using "$path/0 Data/_Processed/US_Bank_Alt_Equity_Beta"
keep 		if _merge == 3
drop 		_merge
	
	
* Merge exposure betas (11 proxies from Fahlenbrach et al. RFS 2021)	************************************************************************
	
*** Other exposure betas (see definition in their paper and email from Rudi re/firm specific measure)
* Sophie constructed measures -> where is the do file?
* 	

preserve
	
use "$path/0 Data/_Processed/Beta_daily_KP", clear
drop iid
save "$path/0 Data/_Processed/Beta_daily_KP_no_iid", replace
restore	


rename Ticker tic	
merge 1:1 tic using "$path/0 Data/_Processed/Beta_daily_KP_no_iid"
gen no_beta_2 = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_affectedshareBEA3corrtq beta_affectedshareFHPcorrtq beta_affectedsharetq beta_covidindustryind beta_customersharetq beta_empteleworkablebq beta_laborintensitytq beta_manualclass beta_presencesharetq beta_teamworksharetq beta_yoysaledeltabq{
replace `var' = 0 if `var' == .
}

rename tic Ticker


* merge beta employees
*****************************


merge 1:1 Ticker using "$path/0 Data/_Processed/beta_employee"
drop if _merge == 2
drop _merge

gen no_beta_employee = (beta_employee == .)
replace beta_employee = 0 if beta_employee == .


* merge beta physical
*****************************


merge 1:1 Ticker using "$path/0 Data/_Processed/beta_physical"
drop if _merge == 2
drop _merge

gen no_beta_physical = (beta_physical==.)
replace beta_physical = 0 if beta_physical == .


* merge beta face
*****************************


merge 1:1 Ticker using "$path/0 Data/_Processed/beta_face"
drop if _merge == 2
drop _merge

gen no_beta_face = (beta_face==.)
replace beta_face = 0 if beta_face == .

* merge beta customer
*****************************


merge 1:1 Ticker using "$path/0 Data/_Processed/beta_customer"
drop if _merge == 2
drop _merge

gen no_beta_customer = (beta_customer==.)
replace beta_customer = 0 if beta_customer == .


* merge all exposures 
*****************************


merge 1:1 gvkey using "$path/0 Data/_Processed/Exposure_gvkey_all"
drop if _merge == 2


gen no_exposure = (_merge==1)

foreach var of varlist exposure_gvkey_F-exposure_N_gvkey_CR_EC{
replace `var' = 0 if `var' == .
}
ren _merge _merge_exposures





* merge betas with residuum of market return 
*************************************************

* 1. Koren Petro 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_KP"
gen no_beta_mr_petro = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_res_affectedsharetq - beta_FE_covidindustryind{
replace `var' = 0 if `var' == .
}

 
* 2. Fahlenbrach 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_F"
gen no_beta_mr_f = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_all_resid_F{
replace `var' = 0 if `var' == .
}


* 3. Chodorow-Reich - Employee 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_CR_E"
gen no_beta_mr_cr_e = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_all_resid_CR_E{
replace `var' = 0 if `var' == .
}

* 4. ONET - Physical distance 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_CR_PP"
gen no_beta_mr_cr_pp = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_all_resid_CR_PP{
replace `var' = 0 if `var' == .
}

* 5. ONET - Face to Face 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_CR_FTF"
gen no_beta_mr_cr_ftf = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_all_resid_CR_FTF{
replace `var' = 0 if `var' == .
}


* 6. ONET - External customer 

	
merge 1:1 gvkey using "$path/0 Data/_Processed/Betas_marketresidual_FE_CR_EC"
gen no_beta_mr_cr_ec = (_merge ==1)
drop if _merge == 2
drop _merge	

foreach var of varlist beta_all_resid_CR_EC{
replace `var' = 0 if `var' == .
}



* merge with repayment risk Q2
*****************************
merge m:1 gvkey using "$path/0 Data/_Processed/bank_level_repayment_risk"
drop if _merge == 2
drop _merge

gen drawdown_j_scaled = drawdown_j/exposure_j
gen feestructure_j_scaled = feestructure_j/exposure_j
gen repay_j_scaled = repayment_j/exposure_j
gen repay_j_repayers_scaled = repayment_j_repayers/exposure_j
gen repay_j_nonrepayers_scaled = repayment_j_nonrepayers/exposure_j


gen repay_j_3B_scaled = repayment_j_3B/exposure_j
gen repay_j_allAs_scaled = repayment_j_allAs/exposure_j
gen repay_j_nonIG_scaled = repayment_j_nonIG/exposure_j
gen repay_j_NR_scaled = repayment_j_NR/exposure_j

gen repay_j_rep_3B_scaled = repayment_j_repayers_3B/exposure_j
gen repay_j_rep_allAs_scaled = repayment_j_repayers_allAs/exposure_j
gen repay_j_rep_nonIG_scaled = repayment_j_repayers_nonIG/exposure_j
gen repay_j_rep_NR_scaled = repayment_j_repayers_NR/exposure_j

gen repay_j_nonrep_3B_scaled = repayment_j_nonrepayers_3B/exposure_j
gen repay_j_nonrep_allAs_scaled = repayment_j_nonrepayers_allAs/exposure_j
gen repay_j_nonrep_nonIG_scaled = repayment_j_nonrepayers_nonIG/exposure_j
gen repay_j_nonrep_NR_scaled = repayment_j_nonrepayers_NR/exposure_j



* Drop if missing off-balance-sheet items
*********************************************

drop if   offbs_unused_cc_ta == .

*replace log_Z = 2.2014139 if entity == 2433312 & log_Z == .
drop if log_Z == .



* Orthogonalize loan-asset ratio due to high correlation with unused credit lines
*************************************************************************************

reg loans_asset liquidity_risk
predict oLA, resid		// use this as proxy for loan-asset ratio in regressions



* Export data set for sample restriction/matching purposes
*************************************************************************************

save "$path/0 Data/_Processed/sample_for_main_regressions", replace
